import sqlite3
from datetime import datetime

from common.enum.stock_category_enum import StockCategoryEnum
from .database_pool import DatabasePool
from peewee import *
from typing import List, Optional
from .database_pool import DatabasePool
from dto.stock_info_monitor import AntMonitorStockInfo

# 定义模型类
db = DatabasePool()


class AntMonitorStock(Model):
    # 股票名称
    stock_name = CharField(null=True)
    # 股票代码
    stock_code = CharField()
    # 股票分类
    stock_plates = CharField()
    # 股票积分
    stock_final_point = IntegerField(null=True)
    # 股票关注等级
    stock_focus_level = IntegerField(null=True)
    # 股票关注等级
    stock_focus_level = IntegerField(null=True)
    # 股票风险等级
    stock_risk_level = IntegerField(null=True)
    # 股票当前持股数
    stock_current_number = IntegerField(null=True)
    # 最大持股数
    stock_max_number = IntegerField(null=True)
    # 原始价格
    monitor_origin_price = FloatField(null=True)
    # 当前价格
    monitor_current_price = FloatField(null=True)
    # 盈利比例
    monitor_income_ratio = FloatField(null=True)
    # 盈利金额
    monitor_income_money = FloatField(null=True)
    # 目标比例
    monitor_target_ratio = FloatField(null=True)
    # 止损比例
    monitor_stop_ratio = FloatField(null=True)
    # 股票状态
    stock_status = IntegerField(null=True)
    # 股票分类
    stock_category = IntegerField(null=True)
    # 打标标签
    stock_tag = TextField(null=True)
    # 股票后续建议
    stock_next_advice = TextField(null=True)
    # 30交易日内上涨天数
    month_up_day = IntegerField(null=True)
    # 本周上涨天数
    week_up_day = IntegerField(null=True)
    # 其他信息
    other_info = TextField(null=True)
    # 上传更新时间
    modified_date = DateTimeField(default=datetime.now)

    class Meta:
        database = DatabasePool(max_connections=5).database  # 指定数据库连接
        table_name = "ant_monitor_stock"  # 指定表名


class StockDas:
    # 增加数据
    def add_stock(self, stock_info: AntMonitorStockInfo):
        AntMonitorStock.create(
            stock_name=stock_info.stock_name,
            stock_code=stock_info.stock_code,
            stock_plates = stock_info.stock_plates,
            stock_final_point=stock_info.stock_final_point,
            stock_focus_level=stock_info.stock_focus_level,
            stock_risk_level=stock_info.stock_risk_level,
            stock_current_number=stock_info.stock_current_number,
            stock_max_number=stock_info.stock_max_number,
            monitor_origin_price=stock_info.monitor_origin_price,
            monitor_current_price=stock_info.monitor_current_price,
            monitor_income_ratio=stock_info.monitor_income_ratio,
            monitor_income_money=stock_info.monitor_income_money,
            monitor_target_ratio=stock_info.monitor_target_ratio,
            monitor_stop_ratio=stock_info.monitor_stop_ratio,
            stock_status=stock_info.stock_status,
            stock_category=stock_info.stock_category,
            stock_tag=stock_info.stock_tag,
            stock_next_advice=stock_info.stock_next_advice,
            month_up_day=stock_info.month_up_day,
            week_up_day=stock_info.week_up_day,
            other_info=stock_info.other_info,
            modified_date=datetime.now(),  # 或者使用默认值
        )

    def update_stock_bycode(self, stock_info: AntMonitorStockInfo):

        stock = AntMonitorStock.get(AntMonitorStock.stock_code == stock_info.stock_code)
        # 更新实例属性
        if stock_info.stock_focus_level is not None:
            stock.stock_focus_level = stock_info.stock_focus_level
        if stock_info.stock_final_point is not None:
            stock.stock_final_point = stock_info.stock_final_point
        if stock_info.stock_risk_level is not None:
            stock.stock_risk_level = stock_info.stock_risk_level
        if stock_info.stock_current_number is not None:
            stock.stock_current_number = stock_info.stock_current_number
        if stock_info.stock_max_number is not None:
            stock.stock_max_number = stock_info.stock_max_number
        if stock_info.monitor_origin_price is not None:
            stock.monitor_origin_price = stock_info.monitor_origin_price
        if stock_info.monitor_current_price is not None:
            stock.monitor_current_price = stock_info.monitor_current_price
        if stock_info.monitor_income_ratio is not None:
            stock.monitor_income_ratio = stock_info.monitor_income_ratio
        if stock_info.monitor_income_money is not None:
            stock.monitor_income_money = stock_info.monitor_income_money
        if stock_info.monitor_target_ratio is not None:
            stock.monitor_target_ratio = stock_info.monitor_target_ratio
        if stock_info.monitor_stop_ratio is not None:
            stock.monitor_stop_ratio = stock_info.monitor_stop_ratio
        if stock_info.stock_status is not None:
            stock.stock_status = stock_info.stock_status
        if stock_info.stock_category is not None:
            stock.stock_category = stock_info.stock_category
        if stock_info.stock_tag is not None:
            stock.stock_tag = stock_info.stock_tag
        if stock_info.stock_next_advice is not None:
            stock.stock_next_advice = stock_info.stock_next_advice
        if stock_info.month_up_day is not None:
            stock.month_up_day = stock_info.month_up_day
        if stock_info.week_up_day is not None:
            stock.week_up_day = stock_info.week_up_day
        if stock_info.other_info is not None:
            stock.other_info = stock_info.other_info

        # 更新修改时间
        stock.modified_date = datetime.now()

        # 保存更改
        stock.save()

    def get_stocks(self):
        stocks = AntMonitorStock.select()
        return list(stocks)

    def get_stocks_code(self):
        stocks = AntMonitorStock.select(AntMonitorStock.stock_code)
        return [stock.stock_code for stock in stocks]

    def delete_bycode(self, stock_code):
        try:
            # 查找记录
            stock = AntMonitorStock.get(AntMonitorStock.stock_code == stock_code)
            # 删除记录
            stock.delete_instance()
            AntLogger.info(f"股票代码 {stock_code} 的数据已被删除。")
        except DoesNotExist:
            AntLogger.info(f"未找到股票代码 {stock_code} 的记录。")

    # 查询数据
    def get_stocks_byCode(self, stock_code):
        try:
            # 使用 get() 查询单条记录
            stock = AntMonitorStock.get(AntMonitorStock.stock_code == stock_code)
            # 直接返回 AntMonitorStock 对象
            return stock

        except AntMonitorStock.DoesNotExist:
            AntLogger.info(f"查询不到股票信息: {stock_code}")
            return None
        except Exception as e:
            AntLogger.info(f"An error occurred: {e}")
            return None

    # 按照股票类别查询数据
    def get_stocks_byCategory(self, category: StockCategoryEnum):
        stocks = AntMonitorStock.select().where(
            AntMonitorStock.stock_category == category.description
        )  # 当前持股
        return list(stocks)

    # 查询按风险等级和盈利比例排序的股票
    def get_stocks_by_risk_level(self):
        stocks = (
            AntMonitorStock.select()
            .where(AntMonitorStock.stock_current_number > 0)  # 当前持股
            .order_by(
                AntMonitorStock.monitor_income_ratio, AntMonitorStock.stock_risk_level
            )  # 盈利比例升序，风险等级升序
        )
        return list(stocks)

    def query_stocks_by_category(
        self, stock_category: int, sort_by: str = "focus_level"
    ) -> List[AntMonitorStock]:
        """
        根据 `stock_category` 查询股票，同时按 `stock_focus_level` 或 `stock_risk_level` 排序。

        :param stock_category: 股票分类
        :param sort_by: 排序依据，可选值 'focus_level' 或 'risk_level'
        :return: 查询结果列表
        """
        # 验证排序字段是否合法
        if sort_by not in ["focus_level", "risk_level"]:
            raise ValueError(
                "Invalid sort_by value. Must be 'focus_level' or 'risk_level'."
            )

        # 动态字段映射
        sort_field = (
            AntMonitorStock.stock_focus_level
            if sort_by == "focus_level"
            else AntMonitorStock.stock_risk_level
        )

        # 查询并排序
        query = (
            AntMonitorStock.select()
            .where(AntMonitorStock.stock_category == stock_category)
            .order_by(sort_field.asc(nulls_last=True))
        )

        # 执行查询并返回结果
        return list(query)

    # 查询按关注等级和盈利比例排序的股票
    def get_stocks_by_focus_level(self):
        stocks = (
            AntMonitorStock.select()
            .where(AntMonitorStock.stock_current_number > 0)  # 当前持股
            .order_by(
                -AntMonitorStock.monitor_income_ratio,
                -AntMonitorStock.stock_focus_level,
            )  # 盈利比例降序，关注等级降序
        )
        return list(stocks)
